package cn.yutong.framework.util;

import com.google.common.base.Strings;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
		response.setHeader("Access-Control-Expose-Headers:", "Content-Disposition");

		response.setHeader("Content-Disposition", "attachment;filename="
				+ URLEncoder.encode(new StringBuilder().append(fileName).append(".xlsx").toString(), "utf-8"));
		exportExcel(data, response.getOutputStream());
	}

	public static int commonExcel(ExcelData data, String exportPath) throws IOException {
		File f = new File(exportPath);
		FileOutputStream fos = new FileOutputStream(f);
		return exportExcel(data, fos);
	}

	private static int exportExcel(ExcelData data, OutputStream outputStream) throws IOException {
		Workbook workbook = new XSSFWorkbook();
		int rowInx = 0;
		try {
			String sheetName = data.getSheetName();
			if (Strings.isNullOrEmpty(sheetName))
				sheetName = "sheet1";
			Sheet sheet = workbook.createSheet(sheetName);
			rowInx = writeExcel(workbook, sheet, data);
			workbook.write(outputStream);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			outputStream.close();
		}
		return rowInx;
	}

	private static int writeExcel(Workbook workbook, Sheet sheet, ExcelData data) {
		int rowInx = 0;
		rowInx = writeTitlesToExcel(workbook, sheet, data.getTitles());
		rowInx = writeRowsToExcel(workbook, sheet, data.getRows(), rowInx);
		autoSizeColumns(sheet, data.getTitles().size() + 1);
		return rowInx;
	}

	private static int writeTitlesToExcel(Workbook workbook, Sheet sheet, List<String> titles) {
		Font font = workbook.createFont();
		font.setFontName("simsun");
		font.setBold(true);
		font.setFontHeightInPoints((short) 10);
		font.setColor(IndexedColors.BLACK.index);

		CellStyle style = workbook.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setFont(font);

		setBorder(style, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

		int rowInx = 0;
		Row row = sheet.createRow(rowInx);
		row.setHeightInPoints(30.0F);

		int colInx = 0;
		for (String title : titles) {
			Cell cell = row.createCell(colInx);
			cell.setCellValue(title);
			cell.setCellStyle(style);
			colInx++;
		}
		rowInx++;
		return rowInx;
	}

	private static int writeRowsToExcel(Workbook wb, Sheet sheet, List<List<Object>> rows, int rowInx) {
		Font font = wb.createFont();
		font.setFontName("simsun");
		font.setFontHeightInPoints((short) 10);
		font.setColor(IndexedColors.BLACK.index);

		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.LEFT);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setFont(font);

		setBorder(style, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

		for (List<Object> rowData : rows) {
			Row dataRow = sheet.createRow(rowInx);
			dataRow.setHeightInPoints(20.0F);
			int colIndex = 0;
			for (Object cellData : rowData) {
				Cell cell = dataRow.createCell(colIndex);
				if ((cellData != null) && (!Strings.isNullOrEmpty(cellData.toString()))) {
					cell.setCellValue(cellData.toString());
				} else {
					cell.setCellValue("");
				}
				cell.setCellStyle(style);
				colIndex++;
			}
			rowInx++;
		}
		return rowInx;
	}

	private static void autoSizeColumns(Sheet sheet, int colNum) {
		for (int i = 0; i < colNum; i++) {
			int orgWidth = sheet.getColumnWidth(i);
			sheet.autoSizeColumn(i, true);
			int newWidth = sheet.getColumnWidth(i) + 100;
			if (newWidth > orgWidth) {
				sheet.setColumnWidth(i, newWidth);
			} else {
				sheet.setColumnWidth(i, orgWidth);
			}
		}
	}

	private static void setBorder(CellStyle style, BorderStyle border, short color) {
		style.setBorderTop(border);
		style.setBorderLeft(border);
		style.setBorderRight(border);
		style.setBorderBottom(border);

		style.setTopBorderColor(color);
		style.setLeftBorderColor(color);
		style.setRightBorderColor(color);
		style.setBottomBorderColor(color);
	}
}
